package com.kcsj.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.kcsj.model.Order;
import com.kcsj.model.OrderItem;
import com.kcsj.utils.DBUtil;

public class OrderDao {

	public Order getOrder(int orderId) throws SQLException {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		String sql = "select * from orderform where id = ?";
		return r.query(sql, new BeanHandler<Order>(Order.class), orderId);
	}

	public void insertOrder(Connection con, Order order) throws SQLException {
		QueryRunner r = new QueryRunner();
		String sql = "insert into orderform(total,amount,status,paytype,name,phone,address,datetime,user_id) values(?,?,?,?,?,?,?,?,?)";
		r.update(con, sql, order.getTotal(), order.getAmount(), order.getStatus(), order.getPaytype(), order.getName(),
				order.getPhone(), order.getAddress(), order.getDatetime(), order.getUser().getId());
	}

	public int getLastInsertId(Connection con) throws SQLException {
		QueryRunner r = new QueryRunner();
		String sql = "select last_insert_id()";
		BigInteger bi = r.query(con, sql, new ScalarHandler<BigInteger>());
		return Integer.parseInt(bi.toString());
	}

	public void insertOrderItem(Connection con, OrderItem item) throws SQLException {
		QueryRunner r = new QueryRunner();
		String sql = "insert into orderitem(price,amount,goods_id,order_id) values(?,?,?,?)";
		r.update(con, sql, item.getPrice(), item.getAmount(), item.getGoods().getId(), item.getOrder().getId());
	}

	public List<Order> selectAll(int userid) throws SQLException {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		String sql = "select * from orderform where user_id=? order by datetime desc";
		return r.query(sql, new BeanListHandler<Order>(Order.class), userid);
	}

	public List<OrderItem> selectAllItem(int orderid) throws SQLException {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		String sql = "select i.id,i.price,i.amount,i.goods_id goodsId,i.order_id orderId,g.name from orderitem i,goods g where order_id=? and i.goods_id=g.id";
		return r.query(sql, new BeanListHandler<OrderItem>(OrderItem.class), orderid);
	}

	public int getOrderCount(int status) throws SQLException {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		String sql = "";
		if (status == 0) {
			sql = "select count(*) from orderform";
			return r.query(sql, new ScalarHandler<Long>()).intValue();
		} else {
			sql = "select count(*) from orderform where status=?";
			return r.query(sql, new ScalarHandler<Long>(), status).intValue();
		}
	}

	public List<Order> selectOrderList(int status, int pageNo, int pageSize) throws SQLException {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		if (status == 0) {
			String sql = "select o.id,o.total,o.amount,o.status,o.paytype,o.name,o.phone,o.address,o.datetime,u.username from orderform o,user u where o.user_id=u.id order by o.datetime desc limit ?,?";
			return r.query(sql, new BeanListHandler<Order>(Order.class), (pageNo - 1) * pageSize, pageSize);
		} else {
			String sql = "select o.id,o.total,o.amount,o.status,o.paytype,o.name,o.phone,o.address,o.datetime,u.username from orderform o,user u where o.user_id=u.id and o.status=? order by o.datetime desc limit ?,?";
			return r.query(sql, new BeanListHandler<Order>(Order.class), status, (pageNo - 1) * pageSize, pageSize);
		}
	}

	public void updateStatus(int id, int status) throws SQLException {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		String sql = "update orderform set status=? where id = ?";
		r.update(sql, status, id);
	}

	public void deleteOrder(Connection con, int id) throws SQLException {
		QueryRunner r = new QueryRunner();
		String sql = "delete from orderform where id = ?";
		r.update(con, sql, id);
	}

	public void deleteOrderItem(Connection con, int id) throws SQLException {
		QueryRunner r = new QueryRunner();
		String sql = "delete from orderitem where order_id=?";
		r.update(con, sql, id);
	}

}
